package Datebase;

import javax.swing.table.DefaultTableCellRenderer;
import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.net.URL;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.border.EmptyBorder;
import javax.swing.GroupLayout.Alignment;
import javax.swing.text.StyledDocument;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class sscore extends JFrame{
    JFrame frame2;

    public static void main(String[] args) {
        System.setProperty("sun.java2d.noddraw", "true");
        new sscore("jasda");
    }

    public sscore(String sno){
        JFrame student = new JFrame("学生成绩查询页面");

        URL resource = sscore.class.getClassLoader().getResource("kun.png");
        Image image = new ImageIcon(resource).getImage();
        student.setIconImage(image);
        //窗口尺寸
        student.setSize(800, 600);
        student.setLocationRelativeTo(null);

        student.setLayout(null);

        JLabel sele = new JLabel("请输入你要查询的学期:");

        sele.setForeground(new Color(0x0A0A0A));
        sele.setFont(new Font("黑体", Font.PLAIN, 18));
        sele.setBounds(70, 380, 200, 100);
        student.add(sele);
//添如用户名输入帼
        JTextField use = new JTextField(20);
        use.setFont(new Font("黑体", Font.PLAIN, 18));
        use.setSelectedTextColor(new Color(0x0A0A0A));
        use.setBounds(280, 410, 200, 40);
        student.add(use);

        JButton button6 = new JButton("查询");
        button6.setFont(new Font("黑体", Font.PLAIN, 15));

        button6.setBounds(510, 410, 100, 40);
        student.add(button6);
        button6.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    String dbURL = "jdbc:oracle:thin:@localhost:1521:XE";
                    String user = "HR";
                    String password = "root";
                    Connection conn = DriverManager.getConnection(dbURL, user, password);
                    String sql1 = "SELECT TERM FROM COURSE  WHERE TERM = '"+use.getText()+"'";
                    PreparedStatement pstmd = conn.prepareStatement(sql1);
                    ResultSet rs1 = pstmd.executeQuery();
                    int count1 = 0;
                    while (rs1.next()) {
                        count1++;
                    }
                    if(count1 == 0){
                        JOptionPane.showMessageDialog(null, "输入的学期有误！",
                                "提示", JOptionPane.WARNING_MESSAGE);
                    }
                    else {
                        new term1(use.getText(),sno);
                    }

                }catch (ClassNotFoundException cnfe) {
                    cnfe.printStackTrace();
                    JOptionPane.showMessageDialog(null, "数据源错误", "错误", JOptionPane.ERROR_MESSAGE);
                }catch (SQLException sqle) {
                    sqle.printStackTrace();
                    JOptionPane.showMessageDialog(null, "数据操作错误", "错误", JOptionPane.ERROR_MESSAGE);
                }


            }
        });

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String dbURL = "jdbc:oracle:thin:@localhost:1521:XE";
            String user = "HR";
            String password = "root";
            Connection conn = DriverManager.getConnection(dbURL, user, password);
            String sql = "SELECT CNAME,GRADE,TERM FROM COURSE,EXAM WHERE COURSE.CNO=EXAM.CNO AND  EXAM.SNO='"+ sno +"' ";
            PreparedStatement pstm = conn.prepareStatement(sql);
            ResultSet rs = pstm.executeQuery();
            int count = 0;
            while (rs.next()) {
                count++;
            }
                System.out.println(count);
                rs = pstm.executeQuery();

                Object[][] data = new Object[count][7];
                String[] title = {"课程名", "成绩","学期"};
                count = 0;
                while (rs.next()) {
                    data[count][0] = (rs.getString("CNAME"));
                    data[count][1] = (rs.getString("GRADE"));
                    data[count][2] = (rs.getString("TERM"));
                    count++;
                }
                JTable table = new JTable(data, title);
                JScrollPane scrollPane = new JScrollPane(table);
                DefaultTableCellRenderer cr = new DefaultTableCellRenderer();
                cr.setHorizontalAlignment(SwingConstants.CENTER);
                table.setDefaultRenderer(Object.class, cr);

                table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
                table.getColumnModel().getColumn(0).setPreferredWidth(200);
                table.getColumnModel().getColumn(1).setPreferredWidth(200);
                table.getColumnModel().getColumn(2).setPreferredWidth(200);
                table.setRowHeight(80);
                table.setFont(new Font("微软雅黑", Font.BOLD, 17));
                scrollPane.setBounds(70, 40, 650, 300);
                student.add(scrollPane);


        }
        catch (ClassNotFoundException cnfe) {
            cnfe.printStackTrace();
            JOptionPane.showMessageDialog(null, "数据源错误", "错误", JOptionPane.ERROR_MESSAGE);
        }catch (SQLException sqle) {
            sqle.printStackTrace();
            JOptionPane.showMessageDialog(null, "数据操作错误", "错误", JOptionPane.ERROR_MESSAGE);
        }

        student.setVisible(true);

    }
}
